Jak vyskriptovat data z tabulky

Pomocí wizardu

Pro jednorázové akce je nejjednodušší si to vyklikat ve wizardu. V Object Exploreru nad databází pravá myš a Tasks/Generate Scripts… Next, abychom se dostali na záložku Choose Objects, kde vybereme konkrétní tabulky, pro které potřebuje vyskriptovat data.
generate scripts1

Na další záložce vybereme, kde chceme skript uložit, třeba Open in new query window. A pak klikneme na tlačítko Advanced, kde vybereme jestli chceme skriptovat data, strukturu, nebo obojí. Já volím Data only. By default tam je Schema only a data bychom tak nedostali.

generate scripts2

Dvakrát Next a Finish a v novém okně máme insert skripty.

SET IDENTITY_INSERT [dbo].[Kategorie] ON 
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, N'domacnost', N'domácnost', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Kategorie] ([IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, N'podnikatel', N'podnikatel', CAST(N'2021-09-30T08:53:06.287' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] ON 
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (1, 1, N'dum', N'dům', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (2, 1, N'byt', N'byt', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (3, 1, N'chata', N'chata', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (4, 2, N'garaz', N'garáž', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (5, 2, N'provozovna', N'provozovna', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[KategorieTyp] ([IdKategorieTyp], [IdKategorie], [Kod], [Nazev], [PlatnostOd], [PlatnostDo]) VALUES (6, 2, N'kancelar', N'kacelář', CAST(N'2021-09-30T08:53:06.310' AS DateTime), CAST(N'2099-12-31T00:00:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[KategorieTyp] OFF
GO

sp_generate_inserts

Pokud potřebujeme pravidelně přenášet data z databáze do databáze, tak se nám hodí nějaká procedura, která nám insert skript vytvoří. K dostání je třeba tady:https://github.com/lambacck/generate_inserts.

Pak v management studiu přepneme Results to text (Ctrl+T) a spustíme:

EXEC dbo.sp_generate_inserts @table_name = 'Kategorie'

Výsledkem pak je skript:

--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[Kategorie] ON
GO
 
 
PRINT 'Inserting values into [Kategorie]'

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(1,'domacnost','domácnost','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')
INSERT INTO [Kategorie] ([IdKategorie],[Kod],[Nazev],[PlatnostOd],[PlatnostDo])VALUES(2,'podnikatel','podnikatel','Sep 30 2021  8:53:06:287AM','Dec 31 2099 12:00:00:000AM')

PRINT 'Done'
 
 
SET IDENTITY_INSERT [dbo].[Kategorie] OFF
GO
SET NOCOUNT OFF